package com.winit.requirement.repository;

import com.winit.requirement.model.BrdRequirement;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import javax.transaction.Transactional;
import java.util.List;

public interface BrdRequirementRepository extends CrudRepository<BrdRequirement, String>{
    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.req_is_del = ?2 where mr.brd_id = ?1", nativeQuery = true)
    int updateIsDeletedByBrdId(String brdId, String isDel);
    @Query(value = "select mr.* from matrix_requirements mr where 1=1 AND(?1 is null or mr.brd_id=?1) AND(?2 is null or mr.project_plan_id=?2) AND(?3 is null or mr.req_id=?3) AND mr.req_is_del <> ?4 order by mr.req_create_time desc", nativeQuery = true)
    List<BrdRequirement> searchBrdRequirements(String brdId, String projectPlanId, String reqId, String excludeIsdel);


    @Query(value = "select mr.* from matrix_requirements mr where 1=1 AND(?1 is null or mr.brd_id=?1) AND(?2 is null or mr.project_plan_id=?2) AND(?3 is null or mr.req_id=?3) AND(?4 is null or mr.product_line_id=?4) AND(?5 is null or mr.req_status=?5) AND(?6 is null or mr.req_charger_account=?6) AND (?7 is null or (mr.req_no = ?7 OR mr.req_name LIKE concat('%', ?7, '%') OR mr.req_desc LIKE concat('%', ?7, '%'))) AND mr.req_is_del <> ?8 order by mr.req_create_time desc", nativeQuery = true)
    List<BrdRequirement> searchBrdRequirementsByMultiCondition(String brdId, String projectPlanId, String reqId, String productLineId, String status, String chargerAccount, String keyword, String excludeIsdel);


    List<BrdRequirement> findByBrdId(String brdId);

    @Query(value = "select mr.* from  matrix_requirements mr where mr.req_id in ?1", nativeQuery = true)
    List<BrdRequirement> searchBrdRequirementsByReqIds(List<String> reqIds);

    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.project_plan_id = ?2, mr.req_status = ?3 where mr.project_plan_id=?1", nativeQuery = true)
    int updateProjectPlanIdAndBrdStatusByProjectPlanId(String oriProjectPlanId, String targetProjectPlanId, String projectPlanStatus);

    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.project_plan_id = ?2 where mr.project_plan_id=?1", nativeQuery = true)
    int updateProjectPlanIdByProjectPlanId(String oriProjectPlanId, String targetProjectPlanId);


    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set  mr.req_status = ?2 where mr.project_plan_id=?1 AND mr.req_status <> 'COMPLETED'", nativeQuery = true)
    int updateNotOnlineBrdStatusByProjectPlanId(String oriProjectPlanId, String projectPlanStatus);


    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.project_plan_id = ?2, mr.req_status = ?3 where mr.req_id in ?1", nativeQuery = true)
    int updateProjectPlanIdAndBrdStatusByReqIds(List<String> reqIds, String projectPlanId, String brdStatus);


    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.project_plan_id = ?2 where mr.req_id in ?1", nativeQuery = true)
    int updateProjectPlanIdByReqIds(List<String> reqIds, String projectPlanId);

    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.req_status = ?2 where mr.req_id in ?1 AND req_status <> 'COMPLETED'", nativeQuery = true)
    int updateBrdStatusByReqIdsIfNotOnline(List<String> reqIds, String brdStatus);

    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.project_plan_id = ?2, mr.req_status = ?3 where mr.req_id = ?1", nativeQuery = true)
    int updateProjectPlanIdAndBrdStatusByReqId(String reqId, String projectPlanId, String status);

    List<BrdRequirement> findByProjectPlanId(String projectPlanId);

    List<BrdRequirement> findByReqName(String reqName);

    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.features = ?2 where mr.req_id = ?1", nativeQuery = true)
    void updateFeaturesByReqId(String reqId, String features);

    @Modifying
    @Transactional
    @Query(value = "update matrix_requirements mr set mr.issues = ?2 where mr.req_id = ?1", nativeQuery = true)
    void updateIssuesByReqId(String reqId, String issues);

    @Query(value = "select mr.* from  matrix_requirements mr where mr.issues like %?1%", nativeQuery = true)
    List<BrdRequirement> getIssueIdBrdRequirement(String issueId);

}
